﻿using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
    public class CauThu_DAO
    {
        //Lấy danh sách cầu thủ
        static public DataTable SelectAll()
        {
            string sql = "select * from CAUTHU";
            return DataAccess.executeQuery(sql);
        }
        static public DataTable SelectTopScores()
        {
            string sql = "select top 20 * from CAUTHU ORDER BY BanThang"; 
            return DataAccess.executeQuery(sql);
        }
        public static DataTable SelectbyDoiBong(int id)
        {
            string sql = "select MaCauThu, TenCauThu, QuocTich, SoAo, MaVT, BanThang, TheVang, TheDo " 
                            + " from CAUTHU where MaDoiBong = " + id;
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        public static DataTable SelectTenbyDoiBong(int id)
        {
            string sql = "select MaCauThu, TenCauThu, SoAo, QuocTich, MaVT from CAUTHU where MaDoiBong = " + id;
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        public static DataTable SelectbyTenAndDoiBong(string ten, int id)
        {
            string sql = "select * from CAUTHU where MaDoiBong = " + id +
                                    " and TenCauThu = '" + ten + "'";
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        public static DataTable SelectbyTenSoAoAndDoiBong(string ten, int id, int soao)
        {
            string sql = "select * from CAUTHU where MaDoiBong = " + id +
                                    " and TenCauThu = '" + ten +
                                    "' and SoAo = " + soao + "";
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        public static DataTable SelectbySoAoAndDoiBong(int id, int soao)
        {
            string sql = "select * from CAUTHU where MaDoiBong = " + id +
                                    " and SoAo = " + soao + "";
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        public static CauThu_DTO SelectByID(int id)
        {
            string sql = "select * from CAUTHU where MaCauThu = " + id;
            DataTable dt = DataAccess.executeQuery(sql);
            CauThu_DTO dto = new CauThu_DTO();
            dto.MaDoiBong = Convert.ToInt32(dt.Rows[0]["MaDoiBong"]);
            dto.TenCauThu = Convert.ToString(dt.Rows[0]["TenCauThu"]);
            dto.QuocTich = Convert.ToString(dt.Rows[0]["QuocTich"]);
            dto.MaCauThu = Convert.ToInt32(dt.Rows[0]["MaCauThu"]);
            dto.SoAo = Convert.ToInt32(dt.Rows[0]["SoAo"]);
            dto.MaViTri = Convert.ToString(dt.Rows[0]["MaVT"]);
            dto.TheVang = Convert.ToInt32(dt.Rows[0]["TheVang"]);
            dto.BanThang = Convert.ToInt32(dt.Rows[0]["BanThang"]);
            dto.TheDo = Convert.ToInt32(dt.Rows[0]["TheDo"]);

            return dto;
        }
        public static DataTable SelectByTen(string ten)
        {
            string sql = "select * from CAUTHU where TenCauThu = '" + ten + "'";
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        public static void Insert(CauThu_DTO dto)
        {
            string sql = "Insert INTO CAUTHU (TenCauThu, MaDoiBong, SoAo, QuocTich, MaVT, BanThang, TheVang, TheDo) VALUES ('" +
                                dto.TenCauThu +
                                "'," + dto.MaDoiBong +
                                "," + dto.SoAo+
                                ",'" + dto.QuocTich+
                                "','" + dto.MaViTri +
                                "'," + dto.BanThang +
                                "," + dto.TheVang +
                                "," + dto.TheDo +")";
                        
            DataAccess.executeNonQuery(sql);
        }

        public static void Update(CauThu_DTO dto)
        {
            string sql = "Update CAUTHU set TenCauThu=('" + dto.TenCauThu +
                                            "'),MaDoiBong=(" + dto.MaDoiBong +
                                            "),SoAo=(" + dto.SoAo +
                                            "),QuocTich=('" + dto.QuocTich+
                                            "'),MaVT=('" + dto.MaViTri +
                                            "'),BanThang=(" + dto.BanThang +
                                            "),TheVang=(" + dto.TheVang +
                                            "),TheDo=(" + dto.TheDo + ") where MaCauThu = " + dto.MaCauThu + "";
            
            DataAccess.executeNonQuery(sql);
        }
        public static void Delete(int ma)
        {
            string sql = "Delete from CAUTHU where MaCauThu = " + ma + "";
            DataAccess.executeNonQuery(sql);
        }
        public static void DeleteByDoiBong(int ma)
        {
            string sql = "Delete from CAUTHU where MaDoiBong = " + ma + "";
            DataAccess.executeNonQuery(sql);
        }
    }
}
